﻿using Dapper;
using Domain.Entity.Basic_information;
using Domain.Entity.Order_goods;
using Domain.Entity.System_dictionary;
using Domain.Entity.System_operation;
using Domain.Entity.System_Ordermanagement;
using Domain.IRepository.System_Order;
using IdentityModel.Client;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection.Metadata.Ecma335;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;

namespace EFCore.Repository.System_order
{
    [Dependency(ServiceLifetime.Scoped)]
    public class OrderRepository : IOrderRepository
    {
        public MyDbContext myDbContext { get; set; }

        public OrderRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }
        public int Count(int Ord_state, string Ord_startaddress, string Ord_destination, string Ord_time, string Client_name)
        {
            string sql = $"select * from [dbo].[Orders] where 1=1";
            if (Ord_time != null && Ord_time != "")
            {
                string[] time1 = Ord_time.Split(',');
                sql += $" and [Ord_time]>='{time1[0]}' and [Ord_time]<='{time1[1]}'";
            }
            if (Ord_startaddress != null && Ord_startaddress == "")
            {
                sql += $" and [Ord_startaddress]='{Ord_startaddress}'";
            }
            if (Ord_state != 0)
            {
                sql += $" and [Ord_state]='{Ord_state}'";
            }
            if (Ord_destination != null && Ord_destination != "")
            {
                sql += $" and [Ord_destination]='{Ord_destination}'";
            }
            if (Client_name != null && Client_name != "")
            {
                sql += $" and [Client_name] like '%{Client_name}%'";
            }
            return myDbContext.Database.GetDbConnection().Query(sql).Count();
        }

        public List<Orders> GetOrders(int currentPage, int PageSize)
        {
            List<Orders> list = myDbContext.Orders.ToList();
            var ordersList = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return ordersList;
        }

        public List<Orders> GetOrders_state(int state, int currentPage, int PageSize)
        {
            string sql = $"select * from [dbo].[Orders] where [Ord_state]={state}";
            List<Orders> list = myDbContext.Database.GetDbConnection().Query<Orders>(sql).ToList();
            var orderlist = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return orderlist;
        }

        public List<Sys_dict_data> GetSys_dict_data()
        {
            string sql = $"select * from [dbo].[Sys_dict_data] where Dict_type='Tran_type'";
            List<Sys_dict_data> list = myDbContext.Database.GetDbConnection().Query<Sys_dict_data>(sql).ToList();
            return list;
        }

        public List<Orders> Orders_condition(int Ord_state, string Ord_startaddress, string Ord_destination, string Ord_time, string Client_name, int currentPage, int PageSize)
        {
            string sql = $"select * from [dbo].[Orders] where 1=1";
            if (Ord_time != null && Ord_time != "")
            {
                string[] time1 = Ord_time.Split(',');
                sql += $" and [Ord_time]>='{time1[0]}' and [Ord_time]<='{time1[1]}'";
            }
            if (Ord_startaddress != null && Ord_startaddress == "")
            {
                sql += $" and [Ord_startaddress]='{Ord_startaddress}'";
            }
            if (Ord_state != 0)
            {
                sql += $" and [Ord_state]='{Ord_state}'";
            }
            if (Ord_destination != null && Ord_destination != "")
            {
                sql += $" and [Ord_destination]='{Ord_destination}'";
            }
            if (Client_name != null && Client_name != "")
            {
                sql += $" and [Client_name] like '%{Client_name}%'";
            }
            List<Orders> list = myDbContext.Database.GetDbConnection().Query<Orders>(sql).ToList();
            var order = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return order;
        }

        public List<Order_client> GetOrder_client(string Ord_number)
        {
            string sql = $@"select *,Ct_cl_name from [dbo].[Orders] as a 
                            inner join [dbo].[Customer_ment] as b on a.Carrier_name=b.Ct_cl_name where Ord_number='{Ord_number}'";
            List<Order_client> list = myDbContext.Database.GetDbConnection().Query<Order_client>(sql).ToList();
            return list;

        }

        public List<Order_good_cargo> GetOgc(string Ord_number)
        {
            string sql = $@"select Order_goodsid,b.Ord_id,c.Cg_id,Ord_num,Cg_num,c.Cg_name,Cg_volume,Cg_weight,Cg_total=(select SUM(Cg_num) from [dbo].[Order_good]),
                            Cg_totalvolume=(select SUM(Cg_volume) from [dbo].[Cargo_ment]),
                            Cg_totalweight=(select SUM(Cg_weight) from [dbo].[Cargo_ment])
                            from [dbo].[Order_good] as a
                            inner join [dbo].[Orders] as b on a.Ord_id=b.Ord_id
                            inner join [dbo].[Cargo_ment] as c on a.Cg_id=c.Cg_id
                            where c.Cg_status='1' and a.Ord_number='{Ord_number}'";
            List<Order_good_cargo> list = myDbContext.Database.GetDbConnection().Query<Order_good_cargo>(sql).ToList();
            return list;
        }

        public List<Carrier_ment> GetCarrier_Ments()
        {
            string sql = $@"select * from [dbo].[Carrier_ment] where Is_del='0'";
            return myDbContext.Database.GetDbConnection().Query<Carrier_ment>(sql).ToList();
        }

        public List<Drivers_ment> GetDrivers_Ments(int Dv_carrier)
        {
            string sql = $@"select * from [dbo].[Drivers_ment] where [Dv_carrier]='{Dv_carrier}' and Is_del='0'";
            return myDbContext.Database.GetDbConnection().Query<Drivers_ment>(sql).ToList();
        }

        public List<Drivers_ment> GetDrivers_Ments_id(int Dv_id)
        {
            string sql = $@"select * from [dbo].[Drivers_ment] where [Dv_id]='{Dv_id}'";
            return myDbContext.Database.GetDbConnection().Query<Drivers_ment>(sql).ToList();
        }

        public List<Vehicles_ment> Getvehicles_Ments(int Dv_id)
        {
            string sql = $@"select * from [dbo].[Vehicles_ment] where [Dv_id]='{Dv_id}'";
            return myDbContext.Database.GetDbConnection().Query<Vehicles_ment>(sql).ToList();
        }

        public List<Sys_dict_data> sys_Dict_Datas(int Dv_model, int Dv_length)
        {
            List<Sys_dict_data> list = new List<Sys_dict_data>();
            string sql = $@"select * from [dbo].[Sys_dict_data] where Dict_code='{Dv_model}'";  //查询车型
            List<Sys_dict_data> list2 = myDbContext.Database.GetDbConnection().Query<Sys_dict_data>(sql).ToList();
            list.Add(list2[0]);
            string sql2 = $@"select * from [dbo].[Sys_dict_data] where Dict_code='{Dv_length}'";
            List<Sys_dict_data> list3 = myDbContext.Database.GetDbConnection().Query<Sys_dict_data>(sql2).ToList();
            list.Add(list3[0]);
            return list;
        }

        public List<Order_cargo> Getorder_Cargos(int Ord_id)
        {
            string sql = $@"select * from [dbo].[Order_good] as a
                            inner join [dbo].[Cargo_ment] as b on a.Cg_id=b.Cg_id
                            where a.Ord_id={Ord_id}";
            return myDbContext.Database.GetDbConnection().Query<Order_cargo>(sql).ToList();
        }

        public int Add(Wb_meter wb_Meter)
        {
            string sql = $@"INSERT INTO [dbo].[Wb_meter]([Wb_number],[Wb_status],[Origin_place],[Det_place],[Tran_manager],[Order_time],[Create_time],[Create_by]
                            ,[Last_time],[Last_by],[Plan_time],[Reality_time],[Planget_time],[Realityget_time],[Audit_status],[Payable_status]
                            ,[Dv_id],[Ord_id],[Cr_id],[Vc_id],[Bill_method],[Freight_costs],[Packag_fee],[Other_fee],[Freight_count]
                            ,[Pay_as_you_go],[Prepayment],[Collect],[Periodical_payment],[Pay_count],[Handling_fee])
                           VALUES('{wb_Meter.Wb_number}','{wb_Meter.Wb_status}','{wb_Meter.Origin_place}','{wb_Meter.Det_place}','{wb_Meter.Tran_manager}','{wb_Meter.Order_time}','{wb_Meter.Create_time}','{wb_Meter.Create_by}',
                                  '{wb_Meter.Last_time}','{wb_Meter.Last_by}','{wb_Meter.Plan_time}','{wb_Meter.Reality_time}','{wb_Meter.Planget_time}','{wb_Meter.Realityget_time}','{wb_Meter.Audit_status}'
                                  ,'{wb_Meter.Payable_status}','{wb_Meter.Dv_id}','{wb_Meter.Ord_id}','{wb_Meter.Cr_id}','{wb_Meter.Vc_id}','{wb_Meter.Bill_method}','{wb_Meter.Freight_costs}','{wb_Meter.Packag_fee}',
                                  '{wb_Meter.Other_fee}','{wb_Meter.Freight_count}','{wb_Meter.Pay_as_you_go}','{wb_Meter.Prepayment}','{wb_Meter.Collect}','{wb_Meter.Periodical_payment}','{wb_Meter.Pay_count}','{wb_Meter.Handling_fee}')";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public List<Cargo_ment> GetCargo_Ments()
        {
            string sql = $@"select * from [dbo].[Cargo_ment] where Is_del='0'";
            return myDbContext.Database.GetDbConnection().Query<Cargo_ment>(sql).ToList();
        }

        public List<Customer_ment> GetCustomer_Ments()
        {
            string sql = $@"select * from [dbo].[Customer_ment] where [Is_del]='0'";
            return myDbContext.Database.GetDbConnection().Query<Customer_ment>(sql).ToList();
        }

        public List<Customer_ment> GetCustomer_Ments_name(string Ct_name)
        {
            string sql = $@"select * from [dbo].[Customer_ment] where [Is_del]='0' and  Ct_name='{Ct_name}'";
            return myDbContext.Database.GetDbConnection().Query<Customer_ment>(sql).ToList();
        }

        public int Add(Orders orders)
        {
            string sql = $@"INSERT INTO [dbo].[Orders]([Ord_number],[Ord_startaddress],[Ord_destination],[Client_name],[Ord_state],[Shipments_Contactname],[Shipments_ContactPhone]
                          ,[Shipments_addres],[Receiving_Contactname],[Receiving_ContactPhone],[Receiving_addres],[Shipping_time]
                          ,[Receiving_time],[Ord_type],[Ord_source],[Ord_remark],[Ord_time],[Create_time],[Create_by]
                          ,[LastUpdate_time],[LastUpdate_by],[Del_flag],[Tran_type],[Count],[Totalvolume],[Cg_name],[Bill_method]
                          ,[Weight],[Freight_costs],[Packag_fee],[Handling_fee],[Other_fee],[Freight_count],[Pay_as_you_go],[Prepayment]
                          ,[Collect],[Periodical_payment],[Pay_count],[Carrier_name],[Carrier_contact_name],[Carrier_phone],[Driver_name]
                          ,[Driver_phone],[Car_type],[Car_long],[Car_number],[Receivables_state],[Audit_status],[Take_orders_time],[Cg_type]
                          ,[Dis_mode],[Vc_length],[Vc_model],[Transit_fees],[Fuel_costs],[Service_charge],[Insurance])
                          VALUES('{orders.Ord_number}','{orders.Ord_startaddress}','{orders.Ord_destination}','{orders.Client_name}','{orders.Ord_state}','{orders.Shipments_Contactname}','{orders.Shipments_ContactPhone}',
                                 '{orders.Shipments_addres}','{orders.Receiving_Contactname}','{orders.Receiving_ContactPhone}','{orders.Receiving_addres}','{orders.Shipping_time}',
                                 '{orders.Receiving_time}','{orders.Ord_type}','{orders.Ord_source}','{orders.Ord_remark}','{orders.Ord_time}','{orders.Create_time}','{orders.Create_by}'
                                ,'{orders.LastUpdate_time}','{orders.LastUpdate_by}','{orders.Del_flag}','{orders.Tran_type}','{orders.Count}','{orders.Totalvolume}','{orders.Cg_name}','{orders.Bill_method}',
                                 '{orders.Weight}','{orders.Freight_costs}','{orders.Packag_fee}','{orders.Handling_fee}','{orders.Other_fee}','{orders.Freight_count}','{orders.Pay_as_you_go}','{orders.Prepayment}',
                                 '{orders.Collect}','{orders.Periodical_payment}','{orders.Pay_count}','{orders.Carrier_name}','{orders.Carrier_contact_name}','{orders.Carrier_phone}','{orders.Driver_name}',
                                 '{orders.Driver_phone}','{orders.Car_type}','{orders.Car_long}'
                                ,'{orders.Car_number}','{orders.Receivables_state}','{orders.Audit_status}','{orders.Take_orders_time}','{orders.Cg_type}',
                                '{orders.Dis_mode}','{orders.Vc_length}','{orders.Vc_model}','{orders.Transit_fees}','{orders.Fuel_costs}','{orders.Service_charge}','{orders.Insurance}')";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public List<Orders> GetOrders_number(string Ord_number)
        {
            string sql = $@"select * from [dbo].[Orders] where Ord_number='{Ord_number}'";
            return myDbContext.Database.GetDbConnection().Query<Orders>(sql).ToList();
        }

        public int AddOrders_good(Order_good order_Good)
        {
            string sql = $@"INSERT INTO [dbo].[Order_good]([Ord_id],[Cg_id],[Ord_num],[Cg_num],[Ord_startaddress],[Ord_destination],[Ord_number])
                            VALUES('{order_Good.Ord_id}','{order_Good.Cg_id}','{order_Good.Ord_num}','{order_Good.Cg_num}','{order_Good.Ord_startaddress}','{order_Good.Ord_destination}','{order_Good.Ord_number}')";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public int JD(int Ord_id)
        {
            string sql = $@"update Orders set [Ord_state]=2 where Ord_id={Ord_id}";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public int TH(int Ord_id)
        {
            string sql = $@"update Orders set [Ord_state]=3 where Ord_id={Ord_id}";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public int FC(int Ord_id)
        {
            string sql = $@"update Orders set [Ord_state]=4 where Ord_id={Ord_id}";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public int DZ(int Ord_id)
        {
            string sql = $@"update Orders set [Ord_state]=5 where Ord_id={Ord_id}";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public int HD(int Ord_id)
        {
            string sql = $@"update Orders set [Ord_state]=6 where Ord_id={Ord_id}";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }
    }
}
